package sealte.mytable.util;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.output.Format;
import org.jdom2.output.XMLOutputter;

public class UtilTools {
	
	public static boolean isNumber(String orgNum){
		Pattern pattern = Pattern.compile("[0-9]*");
		return pattern.matcher(orgNum).matches();
	}
	
	public static String exportExcel(String[][] content,OutputStream os){
		String msg = "";
		WritableWorkbook book = null;
		try{
			book = Workbook.createWorkbook(os);
			WritableSheet sheet = book.createSheet("First Page", 0);
			for(int i=0;i<content.length;i++){
				for(int j=0;j<content[i].length;j++){
					Label aCell = new Label(j,i,content[i][j]);
					sheet.addCell(aCell);
				}
			}
			book.write();
			msg = "Export Excel Successfully!";
		}catch(WriteException e){
			return "WriteExcelError!\n" + e.getMessage();
		}catch(IOException ex){
			return "ExportExcelError!\n" + ex.getMessage();
		}finally{
			try{
				if(null!=book){
					book.close();
					book = null;
				}
			}catch(WriteException e){
				return e.getMessage();
			}catch(IOException ex){
				return ex.getMessage();
			}
		}
		return msg;
	}
	
	public static String exportTXT(String[][] content,OutputStream os){
		String msg = "";
		String cont = "";
		for(int i=0;i<content.length;i++){
			for(int j=0;j<content[i].length;j++){
				if(null==content[i][j]){
					content[i][j] = "";
				}
				cont += content[i][j] + "  ";
			}
			cont += "\r\n";
		}
		try {
			os.write(cont.getBytes());
			msg = "Export TXT Successfully!";
		} catch (IOException e) {
			return e.getMessage();
		}
		return msg;
	}
	
	public static String exportXML(String[][] content,OutputStream os){
		String msg = "";
		Document doc = null;
		try {
			doc = new Document();
			Element root = new Element("DataSet");
			doc.setRootElement(root);
			Element contRow = null;
			Element contCol = null;
			for(int i=0;i<content.length;i++){
				contRow = new Element("R_"+i);
				for(int j=0;j<content[i].length;j++){
					if(null==content[i][j]){
						content[i][j] = "_";
					}
					contCol = new Element("C_"+j);
					contCol.setText(content[i][j]);
					contRow.addContent(contCol);
				}
				root.addContent(contRow);
			}
			Format format = Format.getCompactFormat();
			format.setEncoding("utf-8");
			format.setIndent("  ");
			XMLOutputter out = new XMLOutputter(format);
			out.output(doc, os);
			msg = "Export XML Successfully!";
		} catch (Exception e) {
			return e.getMessage();
		}
		return msg;
	}
	
	public static String save2DB(String[][] content,DBConnection dbc,String sql){
		String[] msg = dbc.injectDBDriver();
		if(msg[0].equals("N")){
			return msg[1];
		}else if(msg[0].equals("Y")){
			if(dbc.db_style.equals("MySQL")){
				return save2MySQL(content,dbc,sql);
			}else if(dbc.db_style.equals("Oracle")){
				return save2Oracle(content,dbc,sql);
			}else{
				return "db_style-->What's the fuck!";
			}
		}else{
			return "What's the fuck!";
		}
	}
	
	private static String save2MySQL(String[][] content,DBConnection dbc,String sql){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = dbc.getConnection();
			int index = sql.indexOf("(");
			String table_name = "";
			if(index>0){
				String postfix = sql.substring(0, index);
				int index_table = postfix.toLowerCase().indexOf("table");
				table_name = postfix.substring(index_table+5, index).trim();
				ps = conn.prepareStatement(sql);
				ps.execute();
			}
			if(index<0){
				table_name = sql.trim();	
			}
			String sql_desc = "DESC " + table_name;
			ps = conn.prepareStatement(sql_desc);
			rs = ps.executeQuery();
			Map<String,String> desc_tab = new HashMap<String,String>();
			String field_all = "";
			while(rs.next()){
				desc_tab.put(rs.getString("Field"), rs.getString("Type"));
				field_all += rs.getString("Field") + ";";
			}
			String[] fields = field_all.split(";");
			String sql_insert = "INSERT INTO " + table_name + "(";
			if(!fields[0].endsWith("_ID")){
				sql_insert += fields[0] + ",";
			}
			for(int k=1;k<fields.length-1;k++){
				sql_insert += fields[k] + ",";
			}
			sql_insert += fields[fields.length-1] + ") VALUES(";
			if(!fields[0].endsWith("_ID")){
				for(int m=0;m<fields.length-1;m++){
					sql_insert += "?,";
				}
				sql_insert += "?)";
			}else{
				for(int n=1;n<fields.length-1;n++){
					sql_insert += "?,";
				}
				sql_insert += "?)";
			}
			ps = conn.prepareStatement(sql_insert);
			for(int i=0;i<content.length;i++){
				for(int j=0;j<content[i].length;j++){
					String type = "";
					if(fields[0].endsWith("_ID")){
						type = desc_tab.get(fields[j+1]);
					}else{
						type = desc_tab.get(fields[j]);
					}
					int index_type = type.indexOf("(");
					type = type.substring(0,index_type);
					if(type.equals("int")){
						ps.setInt(j+1, Integer.parseInt(content[i][j]));
					}else if(type.equals("varchar")){
						ps.setString(j+1, content[i][j]);
					}else{
						ps.setString(j+1, content[i][j]);
					}
				}
				ps.execute();
			}
			
		} catch (Exception e) {
			return "Method--save2MySQL-->\n" + e.getMessage();
		} finally {
			dbc.close(conn, ps, rs);
		}
		return "Save to DB successfully!";
	}
	
	private static String save2Oracle(String[][] content,DBConnection dbc,String sql){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = dbc.getConnection();
			int index = sql.indexOf("(");
			String table_name = "";
			if(index>0){
				String postfix = sql.substring(0, index);
				int index_table = postfix.toLowerCase().indexOf("table");
				table_name = postfix.substring(index_table+5, index).trim();
				ps = conn.prepareStatement(sql);
				ps.execute();
			}
			if(index<0){
				table_name = sql.trim();	
			}
			String sql_desc = "SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME='" + table_name + "'";
			ps = conn.prepareStatement(sql_desc);
			rs = ps.executeQuery();
			Map<String,String> desc_tab = new HashMap<String,String>();
			String field_all = "";
			while(rs.next()){
				desc_tab.put(rs.getString("COLUMN_NAME"), rs.getString("DATA_TYPE"));
				field_all += rs.getString("COLUMN_NAME") + ";";
			}
			String[] fields = field_all.split(";");
			String sql_insert = "INSERT INTO " + table_name + "(";
			if(!fields[0].endsWith("_ID")){
				sql_insert += fields[0] + ",";
			}
			for(int k=1;k<fields.length-1;k++){
				sql_insert += fields[k] + ",";
			}
			sql_insert += fields[fields.length-1] + ") VALUES(";
			if(!fields[0].endsWith("_ID")){
				for(int m=0;m<fields.length-1;m++){
					sql_insert += "?,";
				}
				sql_insert += "?)";
			}else{
				for(int n=1;n<fields.length-1;n++){
					sql_insert += "?,";
				}
				sql_insert += "?)";
			}
			ps = conn.prepareStatement(sql_insert);
			for(int i=0;i<content.length;i++){
				for(int j=0;j<content[i].length;j++){
					String type = "";
					if(fields[0].endsWith("_ID")){
						type = desc_tab.get(fields[j+1]);
					}else{
						type = desc_tab.get(fields[j]);
					}
					
					if(type.equals("NUMBER")){
						ps.setInt(j+1, Integer.parseInt(content[i][j]));
					}else if(type.equals("VARCHAR2")){
						ps.setString(j+1, content[i][j]);
					}else{
						ps.setString(j+1, content[i][j]);
					}
				}
				ps.execute();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return "Method--save2Oracle-->\n" + e.getMessage();
		} finally {
			dbc.close(conn, ps, rs);
		}
		return "Save to DB successfully!";
	}
}
